<?php

class Application_Model_DbTable_Buyers extends Zend_Db_Table_Abstract
{

    protected $_name = 'buyer';
        public function getAll($where=null,$order='admin_id',$method,$page,$number){
        $start = ($page-1)*$number;
        $end = $start+$number;
        $db=$this->getAdapter();
        if($where != null){
            $where='where '.$where;
        }
        $query = "select a.* from (select RowNumber = ROW_NUMBER()
                  OVER (ORDER BY c.$order $method),c.* FROM $this->_name c $where) a
                  where a.RowNumber > $start and a.RowNumber <= $end ";
        
        $stmt = $db->query($query);
        return $stmt->fetchAll();

    }
    public function countRows($where=null){
        if($where != null){
            $where='where '.$where;
        }
        $sql="select count(*) as total from $this->_name c $where";
        $db=$this->getAdapter();
        $stmt = $db-> query($sql);
        $row = $stmt->fetchAll();
        $stmt->closeCursor();

        return $row[0]['total'];

    }
     //get Buyer by ID
     public function getBuyer($id)
     {
        $id = (int)$id;

        $row = $this->fetchRow('buyer_id = ' . $id);
        if (!$row) {
        throw new Exception("Could not find row $id");
        }
        return $row->toArray();
      }

     public function updateBuyer($id,$array){
        $this->update($array, 'buyer_id = ' . (int) $id);

      }
     public function deleteBuyer($id) {
        //$this->delete('buyer_id =' . (int) $id);
         $data = array("deleted"=>1);
         $this->update($data,'buyer_id =' . (int) $id);


    }
      //getBuyerDetails , temp for testing purpose
      public function getBuyerDetails(){
      $db=$this->getAdapter();
      $stmt = $db-> query('SELECT username,password FROM buyer');
      $row = $stmt->fetchAll();
      return $row;
      }
      //Updating Buyer Status
      public function updateBuyerStatus($status,$username) {
        $data = array(
            'active' => $status
        );
        $this->update($data, 'username = ' ."'". $username."'");
      }
      //Add Buyer into database
      public function addBuyer($username,$password,$email,$name,$gender,
                               $dob,$address,$homePhone,$handphone,$city,$province,$zip) {


          //convert dob from string into date
          list($d, $m, $y) = explode('-', $dob);
          $mk=mktime(0, 0, 0, $y, $m, $d);
          $dob_disp1=strftime('%Y-%m-%d',$mk);

          $data = array(
          'username' => $username,
          'password' => $password,
          'email'   => $email,
          'name'    => $name,
          'gender'  => $gender,
          'date_birth' => $dob_disp1,
          'address' => $address,
          'home_phone' => $homePhone,
          'mobile_phone' => $handphone,
          //'path' => $avatar,
          'active' => 0,
              'province'=>$province,
              'city'=>$city,
              'zip'=>$zip
        );
    
        $this->insert($data);
    }
     //check whether username is exist or not
      public function checkUser($username){
      $db=$this->getAdapter();
      $stmt = $db-> query('SELECT username FROM buyer');
      $row = $stmt->fetchAll();
      foreach ($row as $user) {
          if($user['username'] == $username){
              return false;
          }
      }
      $stmt->closeCursor();
      return true;
      }
      //Function to check whether email is exist or not
      public function checkEmail($email){

      $db=$this->getAdapter();
      $stmt = $db-> query('SELECT email FROM buyer');
      $row = $stmt->fetchAll();
      foreach ($row as $user) {
          if($user['email'] == $email){
              return false;
          }
      }
      $stmt->closeCursor();
      return true;
      }
      //get Buyer Id by username
      public function getBuyerId($username){
      $db=$this->getAdapter();
      $stmt = $db-> query("SELECT buyer_id FROM buyer where username='$username'");
      $row = $stmt->fetchAll();
     
      $stmt->closeCursor();
      return $row[0]['buyer_id'];
      }
      //Function to verify login
      public function checkLogin($username,$password){
      $db=$this->getAdapter();
      $stmt = $db-> query("SELECT buyer_id FROM buyer where username='$username'
                           and password='".sha1($password)."'");
      $row = $stmt->fetchAll();
      $stmt->closeCursor();
      if(sizeof($row) > 0){
        return true;

      }else{
          return false;
      }
    }
    public function getBuyerByName($name){
        $db=$this->getAdapter();
        $stmt = $db-> query("SELECT * FROM buyer where lower(username) like
                            '%$name%'
                            ");
        $row = $stmt->fetchAll();
        $stmt->closeCursor();

        return $row;
    }
        public function getHighBuy($number){
        $db = $this->getAdapter();
        $sql = "SELECT TOP $number buyer_id,username,buy FROM buyer ORDER BY buy DESC";
        $stmt = $db->query($sql);
        return $stmt->fetchAll();
    }

    public function getHighHit($number){
        $db = $this->getAdapter();
        $sql = "SELECT TOP $number buyer_id,username,hit FROM buyer ORDER BY hit DESC";
        $stmt = $db->query($sql);
        return $stmt->fetchAll();
    }
    public function getSorting($method,$column){
        $db = $this->getAdapter();
        $sql = "SELECT * FROM buyer ORDER BY $column $method";
        $stmt = $db->query($sql);
        return $stmt->fetchAll();
    }
}

